const { Service } = require('egg');

class ScoreService extends Service {
    /**
     * 查询用户今日成绩
     * @param {number} uid 用户id
     */
    async readByToday(uid) {
        let sql = `
            select id, theory, skill, create_time
            from score
            where uid = ? and date_format(create_time, '%Y-%m-%d') = ?
            limit 0, 1
        `;
        // 获取今天的日期格式，如：2020-05-21
        let today = this.ctx.helper.dateFormat(new Date());
        let params = [uid, today];
        let res = await this.app.mysql.query(sql, params);
        return res[0];
    }

    /**
     * 添加成绩
     * @param {number} uid 用户id
     * @param {number} theory 理论成绩
     * @param {number} skill 技能成绩
     */
    async create({ uid, theory, skill }) {
        let sql = `insert into score (uid, theory, skill) values (?, ?, ?)`;
        let params = [uid, theory, skill];
        return await this.app.mysql.query(sql, params);
    }

    /**
     * 获取成绩列表
     * @param {number} pagesize 一页个数
     * @param {number} pagenum 页码
     * @param {number} query 模糊匹配字符串
     * @param {number} create_time 录入时间
     */
    async readListByQuery({ pagesize, pagenum, query, create_time }) {
        let select_sql =
            'select s.id, s.theory, s.skill, s.uid, s.create_time, u.username, u.relname';
        let from_sql = ' from score as s';
        let join_sql = ' join user as u on u.id = s.uid';
        let where_sql = '';
        let params = [];
        // 条件查询
        if (query) {
            where_sql += ' where u.relname like ?';
            params.push('%' + query + '%');
        }
        // 查询当日成绩列表
        if (create_time) {
            // 获取今日时间格式
            let today = this.ctx.helper.dateFormat(new Date(create_time));
            // 如果之前已经有了 where 语句，这里就需要 and 连接
            where_sql += `${
                query ? ' and' : ' where'
            } date_format(s.create_time, '%Y-%m-%d') = ?`;
            params.push(today);
        }
        // 分页参数
        if (pagenum && pagesize) {
            // 统计总数
            let count_sql = 'select count(*) as count';
            // 统计总数
            const countArr = await this.app.mysql.query(
                count_sql + from_sql + join_sql + where_sql,
                params,
            );
            // 分页
            let offset = (pagenum - 1) * pagesize;
            params.push(offset, parseInt(pagesize));
            const dataArr = await this.app.mysql.query(
                select_sql + from_sql + join_sql + where_sql + ' limit ?, ?',
                params,
            );
            return {
                total: countArr[0].count,
                list: dataArr,
            };
        }
        // 没有分页，直接查询结果
        return await this.app.mysql.query(
            select_sql + from_sql + join_sql + where_sql,
            params,
        );
    }

    /**
     * 更新一条记录
     * @param {Object} param0 参数值
     */
    async update({ id, theory, skill }) {
        const row = { id };

        // 存在的参数才更新
        if (theory) {
            row.theory = theory;
        }

        if (skill) {
            row.skill = skill;
        }
        return await this.app.mysql.update('score', row);
    }

    /**
     * 删除成绩
     * @param {Number} id 成绩记录 id
     */
    async deleteById(id) {
        const res = await this.app.mysql.delete('score', { id });
        console.log('res', res);
        if (res.affectedRows < 1) {
            this.ctx.throwRE(40401, '删除失败，不存在该记录');
        }
    }
}

module.exports = ScoreService;
